Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Running the Progress-to-ORACLE utility

You can run the Progress-to-ORACLE utility interactively or in batch mode.

To migrate an OpenEdge database to ORACLE:
  1. Create a target ORACLE database if you do not already have one. If you want to use tablespaces, you must define them in your ORACLE database before running the Progress-to-ORACLE utility. When developing a new DataServer application, start with a new empty database.

  2. Start an instance of your target ORACLE database.

  3. If connecting with ORACLE networking, make sure that the ORACLE_SID environment variable is set to the ORACLE database name.

  4. Make sure that the ORACLE_HOME environment variable is set to the directory where you installed ORACLE.

  5. Start the OpenEdge client and connect to the OpenEdge database that you want to migrate to ORACLE.

    6. Note: For a DBE DataServer application, you must specify the Internal Code Page (-cpinternal) and Stream Code Page (-cpstream) parameters when you start the client. The values that you specify for these parameters much match the code page that the ORACLE database uses.

  6. In Windows, from the Data Admin tool, choose DataServer ORACLE Utilities Schema Migration Tools PROGRESS DB to ORACLE.

    7. On UNIX, access the utility from the DataServer menu in the Data Dictionary. Or, you can run the utility from the command line: 

    pro source_db -p prodict/ora/protoora.p

  7. The following screen appears and prompts you for the information described in the Table 7–7 that follows after the screen in this step:

    8. Table 7–7: Progress to ORACLE UI elements
    Interface element
    Description
    Original Progress Database
    The source database name. Accept the default value.
    Connect parameters for Progress
    The parameters for the connection to the source Progress database, which is the current working database. Accept the default value.
    Name of Schema holder Database
    Enter the name for the schema holder. The utility creates the schema holder if it does not exist.
    Logical name for ORACLE Database
    Enter the ORACLE database logical name. The logical database name is the name of the schema image and the name you will use to refer to the ORACLE database in applications. The database’s logical name must be different than the name you enter for the schema holder and different than the name of any other schema image existing in that schema holder.
    What version of ORACLE
    Enter the version of ORACLE you are using. Possible values are 8, 9, or 10g. The default is 8.
    ORACLE Owner’s Username
    Enter the ORACLE database owner’s name.
    ORACLE User’s password
    Enter the owner’s password.
    Code page for Schema Image
    Enter the OpenEdge name for the code page that the ORACLE database uses.
    Collation Name
    Enter the OpenEdge name for the collation that the ORACLE database uses.
    Tablespace tables
    Enter the name of the ORACLE tablespace where you want to store schema information. The default is the ORACLE user’s default tablespace.
    Interface element
    Description
    Tablespace index
    Enter the name of the ORACLE tablespace where you want to store index information. The default is the ORACLE user’s default tablespace.
    Create Progress RECID field
    Check this toggle box to create the Progress record identifier for FIND PREV/LAST.
    Include default
    Check this toggle box to include initial values in column definitions.
    Use width field
    Check this toggle box to use the _width field to calculate column size instead of using the format field.
    Interface element
    Description
    Load SQL
    Check this toggle box to load the .sql file that contains the data definitions for your OpenEdge database into the ORACLE database. This is the default option.
    Move Data
    Check this toggle box to dump and load data from your OpenEdge database to the ORACLE database. Copying data from a large database can take a long time. For example, you might not check this box if you want to dump and load data at a more convenient time.
    For field widths use:
    When pushing fields to a foreign data source, you can select one of two primary field format options:
    • Width — Uses the value of the _width field in the _field record.
    • 4GL Format — Compiles with the current default width specified. (default)
    Ifyou select the 4GL Format option, you have an additional setting to define:
    • Expand x(8) to 30—This setting is on by default to indicate that the format for the character fields defined as x(8) will be created as 30 characters.

    Note: You cannot use the Expand x(8) to 30 setting with the Width option.

To perform a complete migration of your OpenEdge database to ORACLE, you must enter information in all fields and check all toggle boxes.

The utility creates a schema holder, an ORACLE database that contains the objects from your OpenEdge database, and a startup procedure that you can use to connect to your schema holder. The startup procedure derives its name from the logical name for your ORACLE database. For example, if you specified “orasports” as the logical database name, the utility creates the corasports.p startup procedure.

To run the Progress-to-ORACLE utility in batch mode on a UNIX client machine:
  1. Create a target ORACLE database. You should connect as a user with no pre-existing object.

  2. Start an instance of your target ORACLE database.

  3. If using ORACLE Networking, make sure that the ORACLE_SID environment variable is set to the ORACLE database name.

  4. Make sure that the ORACLE_HOME environment variable is set to the directory where you installed ORACLE.

  5. On your client machine, pass parameters to the utility by setting the environment variables as listed in Table 7–8:

    6. Table 7–8: Environment variables
    Environment variable
    Description
    PRODBNAME
    Specify the source OpenEdge database name.
    PROCONPARMS
    Specify parameters for the connection to the source OpenEdge database.
    SHDBNAME
    Specify the new schema-holder name.
    ORADBNAME
    Specify the ORACLE database logical name. The database’s logical name can be the same as its physical name, but it must be different from the name you enter for the schema holder.
    ORAVERSION
    Specify the version of ORACLE you are using. Possible values are 8, 9, or 10g. The default is 8.
    ORAUSERNAME
    Specify the ORACLE user’s name.
    Environment variable
    Description
    ORAPASSWORD
    Specify the user’s password.
    ORACONPARMS
    Specify additional connection parameters for the schema holder.
    ORACODEPAGE
    Specify the OpenEdge name for the code page that the ORACLE Database uses.
    TABLEAREA
    Enter the name of the ORACLE tablespace where you want to store schema information. The default is the SYSTEM tablespace.
    INDEXAREA
    Enter the name of the ORACLE tablespace where you want to store index information. The default is the SYSTEM tablespace.
    COMPATIBLE
    Specify YES to create an ORACLE database that supports FIND PREV/LAST and the Progress 4GL ROWID function. The default is YES.
    SQLWIDTH
    Specify YES to use the _width field to calculate column size instead of using the format field.
    ORACOLLNAME
    Enter the collation name.
    CRTDEFAULT
    Specify YES to include initial values in column definitions.
    Environment variable
    Description
    LOADSQL
    Specify YES to load the .sql file that contains the data definitions for your OpenEdge database into the ORACLE database. Specify NO if you do not want the utility to load the .sql file, for example if you want to edit the file before loading it. The default is YES.
    MOVEDATA
    Specify YES to dump and load data. Otherwise, if you do not want to populate the ORACLE database, specify NO. For example, you might specify NO if your database is large, and you want to dump and load data at a more convenient time. The default is NO.

  6. Enter these commands to set and export environment variables at the system prompt before running the compiled 4GL program, protoora.r:

    7. PRODBNAME=db-name; export PRODBNAME
PROCONPARMS="-1 -i"
SHDBNAME=schema-holder-name; export SHDBNAME
.
.
.
pro -b -p prodict/ora/protoora.r

Copyright © 2005 Progress Software Corporation
 www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095